const { Pool } = require('pg');
require('dotenv').config({ path: '../backend/.env' });

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

async function verifyTables() {
  try {
    console.log('Connecting to database...');
    
    // Test connection
    const connectionTest = await pool.query('SELECT NOW()');
    console.log('Database connected successfully at:', connectionTest.rows[0].now);
    
    // Check if tables exist
    const tables = ['users', 'categories', 'accounts', 'bills', 'budgets', 'reports', 'ai_suggestions'];
    
    for (const table of tables) {
      try {
        const result = await pool.query(`
          SELECT EXISTS (
            SELECT FROM information_schema.tables 
            WHERE table_schema = 'public' 
            AND table_name = $1
          )
        `, [table]);
        
        if (result.rows[0].exists) {
          console.log(`✓ Table '${table}' exists`);
          
          // Get table structure
          const structure = await pool.query(`
            SELECT column_name, data_type, is_nullable 
            FROM information_schema.columns 
            WHERE table_name = $1
            ORDER BY ordinal_position
          `, [table]);
          
          console.log(`  Structure of '${table}':`);
          structure.rows.forEach(col => {
            console.log(`    ${col.column_name} (${col.data_type}) ${col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'}`);
          });
        } else {
          console.log(`✗ Table '${table}' does not exist`);
        }
      } catch (err) {
        console.error(`Error checking table '${table}':`, err.message);
      }
    }
    
    // Close pool
    await pool.end();
    console.log('Database connection closed');
  } catch (err) {
    console.error('Database connection error:', err.message);
    await pool.end();
  }
}

verifyTables();